package ylsb.service;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.write.*;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestParam;
import ylsb.dao.ShijiMapper;
import ylsb.dao.ShijihaocaijieguoMapper;
import ylsb.dao.YiyongjieguoMapper;
import ylsb.model.*;
import ylsb.support.JsonResult;
import ylsb.support.PageInfo;
import ylsb.support.StringHandle;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
public class ShijijieguoService {

    @Resource
    ShijihaocaijieguoMapper ddMapper;

    @Resource
    YltpuserService ddYltpuserService;

    @Resource
    YltprenyuanfenpeiService ddYltprenyuanfenpeiService;

    @Resource
    YltpXiangmuService ddYltpXiangmuService;

    public Shijihaocaijieguo findOne(String id){
        return ddMapper.selectByPrimaryKey(id);
    }

    public List<Map<String, Object>> findPinpaiJieguo(String ppxiangmuId,String ppuserId,String mmPinzhonxuhao,String mmPinpaixuhao){
        return ddMapper.findPinpaiJieguo(ppxiangmuId,ppuserId,mmPinzhonxuhao,mmPinpaixuhao);
    }
    public List<Map<String, Object>> findPinguiJieguo(String ppxiangmuId,String ppuserId,String ppdetailId,String mmPinzhonxuhao,String mmPinpaixuhao){
        return ddMapper.findPinguiJieguo(ppxiangmuId,ppuserId,ppdetailId,mmPinzhonxuhao,mmPinpaixuhao);
    }

    public List<Map<String, Object>> findPinzhongResult(String ppxiangmuId, String ppuserId,String ppPinzhong){
        return ddMapper.findPinzhongResult(ppxiangmuId,ppuserId,ppPinzhong);
    }

    public List<Map<String, Object>> findFengbiResult(String ppxiangmuId, String ppuserId,String ppPinzhong){
        return ddMapper.findFengbiResult(ppxiangmuId,ppuserId,ppPinzhong);
    }

    public List<Map<String, Object>> findResultGuochanBypinzhong(String ppxiangmuId, String ppuserId,String ppPinzhong){
        return ddMapper.findResultGuochanBypinzhong(ppxiangmuId,ppuserId,ppPinzhong);
    }
    public List<Map<String, Object>> mmPinpaiByPinzhongList(String ppxiangmuId, String ppuserId,int ppPinzhong){
        return ddMapper.mmPinpaiByPinzhongList(ppxiangmuId,ppuserId,ppPinzhong);
    }

    public List<Shijihaocaijieguo> findByDetailIdAndUserId(String ppdetailid,String ppuserid){
        ShijihaocaijieguoExample mmExample=new ShijihaocaijieguoExample();
        ShijihaocaijieguoExample.Criteria criteria = mmExample.createCriteria();
        criteria.andDetailidEqualTo(ppdetailid).andUseridEqualTo(ppuserid);
        return ddMapper.selectByExample(mmExample);
    }

    public Integer getPinguiResultCount(String mmxiangmuId,String mmuserId,String mmresult){
        Object mmCount = ddMapper.getPinguiResultCount(mmxiangmuId,mmuserId,mmresult);
        return mmCount == null ? 0 : Integer.parseInt(mmCount.toString());
    }
    public Integer getPinzhongResultCount(String mmxiangmuId,String mmuserId,String mmresult){
        Object mmCount = ddMapper.getPinzhongResultCount(mmxiangmuId,mmuserId,mmresult);
        return mmCount == null ? 0 : Integer.parseInt(mmCount.toString());
    }

    public Integer checkCountByPinzhongandUserId(int ppPinzhong,String ppuserId,String ppxiangmuId){
        ShijihaocaijieguoExample mmExample=new ShijihaocaijieguoExample();
        ShijihaocaijieguoExample.Criteria criteria = mmExample.createCriteria();
        criteria.andPinzhongEqualTo(ppPinzhong).andUseridEqualTo(ppuserId).andXiangmuidEqualTo(ppxiangmuId).andResultEqualTo("1");
        Object mmCount=ddMapper.selectByExample(mmExample).size();
        return mmCount == null ? 0 : Integer.parseInt(mmCount.toString());
    }
    public Integer checkPinpaiResultCountResult(int ppPinzhong,String ppuserId,String ppxiangmuId){
        Object mmCount = ddMapper.checkPinpaiResultCountResult(ppPinzhong,ppuserId,ppxiangmuId);
        return mmCount == null ? 0 : Integer.parseInt(mmCount.toString());
    }

    public Shijihaocaijieguo checkexistByUserId(String mmdetailId,int ppPinzhong, String ppPinpai, String ppuserId, String ppxiangmuId ){
        ShijihaocaijieguoExample mmExample=new ShijihaocaijieguoExample();
        ShijihaocaijieguoExample.Criteria criteria = mmExample.createCriteria();
        criteria.andDetailidEqualTo(mmdetailId).andPinzhongEqualTo(ppPinzhong).andPinpaiEqualTo(ppPinpai).andUseridEqualTo(ppuserId).andXiangmuidEqualTo(ppxiangmuId);
        List<Shijihaocaijieguo> list =ddMapper.selectByExample(mmExample);
        return !list.isEmpty()? list.get(0): null;
    }

    public boolean modify(Shijihaocaijieguo shijihaocaijieguo){return ddMapper.updateByPrimaryKeySelective(shijihaocaijieguo)==1;}

    public boolean add(Shijihaocaijieguo shijihaocaijieguo){
        return ddMapper.insert(shijihaocaijieguo)==1;
    }

    public void daochuqianzibiao(@RequestParam("xiangmuid") String ppxiangmuid,
                                 @RequestParam("userid") String ppuserid,
                                 HttpServletRequest request, HttpServletResponse response) {

        response.reset();
        response.setContentType("application/x-download;charset=utf-8");
        List<Shijipinzhong> pinzhongs = ddMapper.selectDaochuByXiangmuid(ppxiangmuid, ppuserid);
//        List<Map<String,Object>> mmList = ddService.findDaochuPinzhongAll(ppxiangmuid);
        YltpUser mmuser = ddYltpuserService.findOne(ppuserid);
        String mmusername = mmuser.getUsername();
        YltpXiangmu mmxiangmu = ddYltpXiangmuService.findOne(ppxiangmuid);
        String mmxiangmuname = mmxiangmu.getXiangmuname();
        OutputStream os = null;
        WritableWorkbook wbook = null;
        try {
            os = new BufferedOutputStream(response.getOutputStream());//得到输出流的语句
            //response.getOutputStream();// 取得输出流
            response.reset();// 清空输出流
            SimpleDateFormat mmDateFormat = new SimpleDateFormat("yyyyMMdd-HHmmss");
            String wenjiantitle = mmxiangmuname + mmusername + "专家签字表"; //文件名
            String tmptitle = "《医院医用耗材供应目录》调整表决表（" + mmxiangmuname + "）"; // 标题
            response.setHeader("Content-disposition", "attachment; filename=" + new String(wenjiantitle.getBytes("utf-8"), "iso-8859-1") + ".xls");// 设定输出文件头
            response.setContentType("application/vnd.ms-excel");// 定义输出类型
            wbook = Workbook.createWorkbook(os); // 建立excel文件
            WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称
            // 设置excel标题
            WritableFont wfont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); //设置表格样式

            WritableCellFormat wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);

            WritableCellFormat wcfFC3 = new WritableCellFormat(wfont);
            wcfFC3.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC3.setAlignment(Alignment.CENTRE);
            //wcfFC.setBackground(Colour.AQUA);
            Label label = new Label(0, 0, tmptitle, wcfFC3);

            WritableCellFormat cellFormat = new WritableCellFormat();
            cellFormat.setAlignment(Alignment.CENTRE);
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
            cellFormat.setWrap(true);
            //cellFormat.setBackground(Colour.AQUA);
            cellFormat.setFont(wfont);
            label.setCellFormat(cellFormat);
            wsheet.addCell(label);
            wsheet.addCell(new Label(0, 0, tmptitle, wcfFC3));
            /*wsheet.addCell(new Label(7, 0, tmptitle, wcfFC));*/
            wsheet.setRowView(0, 800); //第一行高度
            wsheet.setRowView(1, 800); //第一行高度
            wsheet.setRowView(2, 660); //第一行高度
            wsheet.mergeCells(0, 0, 18, 0);  //合并单元格(第一列的第一行和第十三列的第一行合并)
            wsheet.setColumnView(0, 9); //类别宽度
            wsheet.setColumnView(1, 9); //序号宽度
            wsheet.setColumnView(2, 9); //品种名宽度
            wsheet.setColumnView(3, 9); //品种序号宽度
            wsheet.setColumnView(4, 9); //品牌宽度
            wsheet.setColumnView(5, 9); //型号宽度
            wsheet.setColumnView(6, 9); //平台编码宽度
            wsheet.setColumnView(7, 9); //产品名称宽度
            wsheet.setColumnView(8, 9); //产品规格宽度
            wsheet.setColumnView(9, 9); //产品型号宽度
            wsheet.setColumnView(10, 9); //注册证号宽度
            wsheet.setColumnView(11, 9); //生产企业宽度
            wsheet.setColumnView(12, 9); //单位宽度
            wsheet.setColumnView(13, 9); //平台情况宽度
            wsheet.setColumnView(14, 9); //进口/国产宽度
            wsheet.setColumnView(15, 9); //参考单价（元）宽度
            wsheet.setColumnView(16, 9); //是否医保宽度
            wsheet.setColumnView(17, 9); //品牌意见宽度
            wsheet.setColumnView(18, 9); //型号意见宽度

            // 开始生成主体内容
            wfont = new WritableFont(WritableFont.createFont("宋体"), 8, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);
            wcfFC.setWrap(true);
            // 不需要边框和居中的样式
            WritableFont wfont2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); //设置表格样式
            WritableCellFormat wcfFC2 = new WritableCellFormat(wfont2);
            wcfFC2.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直居中
            wcfFC2.setAlignment(Alignment.LEFT);
            wcfFC2.setWrap(true);
            //  在  1 列  2 行 写入内容
            wsheet.addCell(new Label(9, 1, "委 员（签字确认）：投票内容已核对无误。" + mmusername, wcfFC2));
            // 把  0 列 2 行 到 9 列 2 行合并
            wsheet.mergeCells(9, 1, 18, 1);

            wsheet.addCell(new Label(0, 1, "会议日期：" + StringHandle.shorttiemString(), wcfFC2));
            wsheet.mergeCells(0, 1, 8, 1);

            // 第三行 内容
            wsheet.addCell(new Label(0, 2, "问题类别", wcfFC));
            /*wsheet.addCell(new Label(1, 2, "序号", wcfFC));*/
            wsheet.addCell(new Label(1, 2, "品种序号", wcfFC));
            wsheet.addCell(new Label(2, 2, "品种名", wcfFC));
            wsheet.addCell(new Label(3, 2, "品牌", wcfFC));
            wsheet.addCell(new Label(4, 2, "型号", wcfFC));
            wsheet.addCell(new Label(5, 2, "平台编码", wcfFC));
            wsheet.addCell(new Label(6, 2, "产品名称", wcfFC));
            wsheet.addCell(new Label(7, 2, "产品规格", wcfFC));
            wsheet.addCell(new Label(8, 2, "产品型号", wcfFC));
            wsheet.addCell(new Label(9, 2, "注册证号", wcfFC));
            wsheet.addCell(new Label(10, 2, "生产企业", wcfFC));
            wsheet.addCell(new Label(11, 2, "单位", wcfFC));
            wsheet.addCell(new Label(12, 2, "平台情况", wcfFC));
            wsheet.addCell(new Label(13, 2, "进口/国产", wcfFC));
            wsheet.addCell(new Label(14, 2, "参考单价（元）", wcfFC));
            wsheet.addCell(new Label(15, 2, "是否医保", wcfFC));
            wsheet.addCell(new Label(16, 2, "上次设备会审议结果", wcfFC));
            wsheet.addCell(new Label(17, 2, "品牌意见", wcfFC));
            wsheet.addCell(new Label(18, 2, "型号意见", wcfFC));

            // 表头结束

            wfont = new WritableFont(WritableFont.createFont("宋体"), 8, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);
            wcfFC.setWrap(true);

            int mmpinzhongKaishihang = 3;
            int mmpinpaiKaishihang = 3;
            int mmxinghaoKaishihang = 3;

            for (Shijipinzhong pinzhong : pinzhongs) {
                int pinpaiSize = 0;
                for (Shijipinpai pinpai : pinzhong.getPinpais()) {
                    int xinghaoSize = pinpai.getXinghaos().size();
                    String mmpinpairesult = "0";
                    for (Shijixinghao xinghao : pinpai.getXinghaos()) {
                        String mmleibie = xinghao.getLeibie() == null ? "" : xinghao.getLeibie();
                        String mmxuhao = xinghao.getOrdernumber() == null ? "" : String.valueOf(xinghao.getOrdernumber());
                        String mmxinghao = xinghao.getXinghao() == null ? "" : xinghao.getXinghao();
                        String mmpingtaibianma = xinghao.getPingtaibianma() == null ? "" : xinghao.getPingtaibianma();
                        String mmchanpinmingcheng = xinghao.getChanpinmingcheng() == null ? "" : xinghao.getChanpinmingcheng();
                        String mmchanpinguige = xinghao.getChanpinguige() == null ? "" : xinghao.getChanpinguige();
                        String mmchanpinxinghao = xinghao.getChanpinxinghao() == null ? "" : xinghao.getChanpinxinghao();
                        String mmzhucezhenghao = xinghao.getZhucezhenghao() == null ? "" : xinghao.getZhucezhenghao();
                        String mmshengchanqiye = xinghao.getShengchanqiye() == null ? "" : xinghao.getShengchanqiye();
                        String mmdanwei = xinghao.getDanwei() == null ? "" : xinghao.getDanwei();
                        String mmpingtaiqingkuang = xinghao.getPingtaiqingkuang() == null ? "" : xinghao.getPingtaiqingkuang();
                        String mmjinkouguochan = xinghao.getJinkouguochan() == null ? "" : xinghao.getJinkouguochan();
                        String mmcankaodanjia = xinghao.getCankaodanjia() == null ? "" : xinghao.getCankaodanjia();
                        String mmyibao = xinghao.getYibao() == null ? "" : xinghao.getYibao();
                        String mmbeizhu = xinghao.getBeizhu() == null ? "" : xinghao.getBeizhu();
                        String mmresult = xinghao.getResult() == null ? "" : xinghao.getResult();
                        String mmyijian;
                        if("通过".equals(mmbeizhu)){
                                mmyijian = "";
                                mmpinpairesult = "2";
                        }else{
                            if ("1".equals(mmresult)) {
                                mmyijian = "同意";
                                mmpinpairesult = "1";
                            } else if ("0".equals(mmresult)) {
                                mmyijian = "不同意";
                            } else {
                                mmyijian = "不同意";
                            }
                        }
                        wsheet.setRowView(mmxinghaoKaishihang, 580); //第三行高度
                        wsheet.addCell(new Label(0, mmxinghaoKaishihang, mmleibie, wcfFC));
                      /*  wsheet.addCell(new Label(1, mmxinghaoKaishihang, mmxuhao, wcfFC));*/
                        wsheet.addCell(new Label(4, mmxinghaoKaishihang, mmxinghao, wcfFC));
                        wsheet.addCell(new Label(5, mmxinghaoKaishihang, mmpingtaibianma, wcfFC));
                        wsheet.addCell(new Label(6, mmxinghaoKaishihang, mmchanpinmingcheng, wcfFC));
                        wsheet.addCell(new Label(7, mmxinghaoKaishihang, mmchanpinguige, wcfFC));
                        wsheet.addCell(new Label(8, mmxinghaoKaishihang, mmchanpinxinghao, wcfFC));
                        wsheet.addCell(new Label(9, mmxinghaoKaishihang, mmzhucezhenghao, wcfFC));
                        wsheet.addCell(new Label(10, mmxinghaoKaishihang, mmshengchanqiye, wcfFC));
                        wsheet.addCell(new Label(11, mmxinghaoKaishihang, mmdanwei, wcfFC));
                        wsheet.addCell(new Label(12, mmxinghaoKaishihang, mmpingtaiqingkuang, wcfFC));
                        wsheet.addCell(new Label(13, mmxinghaoKaishihang, mmjinkouguochan, wcfFC));
                        wsheet.addCell(new Label(14, mmxinghaoKaishihang, mmcankaodanjia, wcfFC));
                        wsheet.addCell(new Label(15, mmxinghaoKaishihang, mmyibao, wcfFC));
                        wsheet.addCell(new Label(16, mmxinghaoKaishihang, mmbeizhu, wcfFC));
                        wsheet.addCell(new Label(18, mmxinghaoKaishihang, mmyijian, wcfFC));

                        mmxinghaoKaishihang += 1;
                    }
                    String mmresult = pinpai.getResult() == null ? "" : pinpai.getResult();
                    String mmpinpaiyijian;
                    if ("1".equals(mmpinpairesult)) {
                        mmpinpaiyijian = "同意";
                    } else if ("0".equals(mmpinpairesult)) {
                        mmpinpaiyijian = "不同意";
                    } else if ("2".equals(mmpinpairesult)){
                        mmpinpaiyijian = "";
                    }else{
                        mmpinpaiyijian = "不同意";
                    }
                    wsheet.addCell(new Label(3, mmpinpaiKaishihang, pinpai.getPinpai(), wcfFC));
                    wsheet.addCell(new Label(17, mmpinpaiKaishihang, mmpinpaiyijian, wcfFC));
                    wsheet.mergeCells(3, mmpinpaiKaishihang, 3, mmpinpaiKaishihang + xinghaoSize - 1);
                    wsheet.mergeCells(17, mmpinpaiKaishihang, 17, mmpinpaiKaishihang + xinghaoSize - 1);
                    mmpinpaiKaishihang += xinghaoSize;
                    pinpaiSize += xinghaoSize;
                }
                wsheet.addCell(new Label(2, mmpinzhongKaishihang, pinzhong.getPinzhongming(), wcfFC));
                wsheet.addCell(new Label(1, mmpinzhongKaishihang, String.valueOf(pinzhong.getPinzhongxuhao()), wcfFC));
                wsheet.mergeCells(2, mmpinzhongKaishihang, 2, mmpinzhongKaishihang + pinpaiSize - 1);
                wsheet.mergeCells(1, mmpinzhongKaishihang, 1, mmpinzhongKaishihang + pinpaiSize - 1);
                mmpinzhongKaishihang += pinpaiSize;
            }
            wbook.write(); // 写入文件
            wbook.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    public JsonResult<?> findHuizongbiaolist(@RequestParam("xiangmuid") String ppxiangmuid) {
        List<Shijipinzhong> pinzhongs = ddMapper.selectHuizongByXiangmuid(ppxiangmuid);
        List<Map<String,Object>> huizongbiaoList = new ArrayList<>();
        List<Map<String, Object>> mmUserList=ddYltprenyuanfenpeiService.findRenyuanFenpeiByXiangmuId(ppxiangmuid);

        int personCount = mmUserList.size();
        double mmPassNumber=2.0 / 3 * personCount;

        for (Shijipinzhong pinzhong : pinzhongs) {
            Map<String, Object> pinzhongMap = new HashMap<>();
            List<Map<String,Object>> returnPinpaiList = new ArrayList<>();
            int pinzhonghebinghangshu = 0;
            for (Shijipinpai pinpai : pinzhong.getPinpais()) {

                Map<String, Object> pinpaiMap = new HashMap<>();
                pinpaiMap.put("pinpaixuhao", pinpai.getPinpai());
                pinpaiMap.put("piaoshu", pinpai.getResult());

                int pinpairesult = ddMapper.selectpinpaipiaoshuByXiangmuidAndPinzhongxuhaoAndPinpai(ppxiangmuid,
                        pinzhong.getPinzhongxuhao(), pinpai.getPinpai());

                pinpaiMap.put("pinpaipiaoshu", pinpairesult + "/" + personCount);
                pinpaiMap.put("passNumber", mmPassNumber);
                pinpaiMap.put("personCount", personCount);
                if (pinpairesult > mmPassNumber) {
                    pinpaiMap.put("pinpairesult", "通过");
                } else {
                    pinpaiMap.put("pinpairesult", "不通过");
                }
                int size = pinpai.getXinghaos().size();
                pinpaiMap.put("pinpaihebinghang", size == 0 ? 1 : size);

                pinpaiMap.put("xinghaolist", pinpai.getXinghaos());
                returnPinpaiList.add(pinpaiMap);
                pinzhonghebinghangshu += size;
            }
            pinzhongMap.put("pinzhongming", pinzhong.getPinzhongming());
            pinzhongMap.put("pinzhongxuhao", pinzhong.getPinzhongxuhao());
            pinzhongMap.put("pinpailist", returnPinpaiList);
            pinzhongMap.put("pinzhonghebinghang", pinzhonghebinghangshu == 0 ? pinzhong.getPinpais().size() : pinzhonghebinghangshu);
            huizongbiaoList.add(pinzhongMap);
        }

        Map<String, Object> returnMap = new HashMap<>();
        returnMap.put("HuizongbiaoList", huizongbiaoList);

        return JsonResult.getSuccessResult(returnMap);
    }

    public void huizongbiao(HttpServletRequest request, HttpServletResponse response,
                            @RequestParam("xiangmuid") String ppxiangmuid,
                            @RequestParam("xiangmuleixing") String ppxiangmuleixing){

        response.reset();
        response.setContentType("application/x-download;charset=utf-8");
        List<Shijipinzhong> pinzhongs = ddMapper.selectHuizongByXiangmuid(ppxiangmuid);
        List<Map<String, Object>> mmUserList=ddYltprenyuanfenpeiService.findRenyuanFenpeiByXiangmuId(ppxiangmuid);

        int personCount = mmUserList.size();
        double mmPassNumber=2.0 / 3 * personCount;

        YltpXiangmu mmxiangmu=ddYltpXiangmuService.findOne(ppxiangmuid);
        String mmxiangmuname=mmxiangmu.getXiangmuname();

        OutputStream os=null;
        WritableWorkbook wbook=null;

        try{
            os = new BufferedOutputStream(response.getOutputStream());//得到输出流的语句
            //response.getOutputStream();// 取得输出流
            response.reset();// 清空输出流
            String wenjiantitle = mmxiangmuname+ "汇总表"; //文件名
            String tmptitle ="《医院医用耗材供应目录》调整表决结果表（" + mmxiangmuname + "）"; // 标题
            response.setHeader("Content-disposition", "attachment; filename="+new String(wenjiantitle.getBytes(),"iso8859-1")+".xls");// 设定输出文件头
            response.setContentType("application/vnd.ms-excel");// 定义输出类型
            wbook = Workbook.createWorkbook(os); // 建立excel文件
            WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称
            // 设置excel标题
            WritableFont wfont = new WritableFont(WritableFont.ARIAL, 14,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); //设置表格样式

            WritableCellFormat wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFC.setVerticalAlignment(jxl.write.VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(jxl.write.Alignment.CENTRE);

            WritableCellFormat wcfFC3 = new WritableCellFormat(wfont);
            wcfFC3.setVerticalAlignment(jxl.write.VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC3.setAlignment(jxl.write.Alignment.CENTRE);
            //wcfFC.setBackground(Colour.AQUA);
            Label label=new Label(0, 0, tmptitle, wcfFC3);

            WritableCellFormat cellFormat=new WritableCellFormat();
            cellFormat.setAlignment(Alignment.CENTRE);
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
            cellFormat.setWrap(true);
            //cellFormat.setBackground(Colour.AQUA);
            cellFormat.setFont(wfont);
            label.setCellFormat(cellFormat);
            wsheet.addCell(label);
            wsheet.addCell(new Label(0, 0, tmptitle, wcfFC3));
            wsheet.setRowView(0,800); //第一行高度
            wsheet.setRowView(1,660); //第一行高度
            wsheet.mergeCells(0, 0, 18, 0);  //合并单元格
            wsheet.setColumnView(0,9); //宽度
            wsheet.setColumnView(1,9); //宽度
            wsheet.setColumnView(2,9); //宽度
            wsheet.setColumnView(3,9); //宽度
            wsheet.setColumnView(4,9); //宽度
            wsheet.setColumnView(5,9); //宽度
            wsheet.setColumnView(6,9); //宽度
            wsheet.setColumnView(7,9); //宽度
            wsheet.setColumnView(8,9); //宽度
            wsheet.setColumnView(9,9); //宽度
            wsheet.setColumnView(10,9); //宽度
            wsheet.setColumnView(11,9); //宽度
            wsheet.setColumnView(12,9); //宽度
            wsheet.setColumnView(13,9); //宽度
            wsheet.setColumnView(14,9); //宽度
            wsheet.setColumnView(15,9); //宽度
            wsheet.setColumnView(16,9); //宽度
            wsheet.setColumnView(17,9); //宽度
           /* wsheet.setColumnView(18,9); //宽度
            wsheet.setColumnView(19,9); //宽度*/

            // 开始生成主体内容
            wfont = new WritableFont(WritableFont.createFont("宋体"), 8,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcfFC.setVerticalAlignment(jxl.write.VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(jxl.write.Alignment.CENTRE);
            wcfFC.setWrap(true);

            // 不需要边框和居中的样式
            WritableFont wfont2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); //设置表格样式
            WritableCellFormat wcfFC2 = new WritableCellFormat(wfont2);
            wcfFC2.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直居中
            wcfFC2.setAlignment(Alignment.LEFT);
            wcfFC2.setWrap(true);

            wsheet.addCell(new Label(0, 1, "会议日期：" + StringHandle.shorttiemString(), wcfFC2));
            wsheet.mergeCells(0, 1, 9, 1);

            wsheet.addCell(new Label(0, 2, "类别",wcfFC));
            /*wsheet.addCell(new Label(1, 2, "序号",wcfFC));*/
            wsheet.addCell(new Label(1, 2, "品种序号",wcfFC));
            wsheet.addCell(new Label(2, 2, "品种名",wcfFC));
            wsheet.addCell(new Label(3, 2, "品牌",wcfFC));
            wsheet.addCell(new Label(4, 2, "型号",wcfFC));
            wsheet.addCell(new Label(5, 2, "平台编码",wcfFC));
            wsheet.addCell(new Label(6, 2, "产品名称",wcfFC));
            wsheet.addCell(new Label(7, 2, "产品规格",wcfFC));
            wsheet.addCell(new Label(8, 2, "产品型号",wcfFC));
            wsheet.addCell(new Label(9, 2, "注册证号",wcfFC));
            wsheet.addCell(new Label(10, 2, "生产企业",wcfFC));
            wsheet.addCell(new Label(11, 2, "单位",wcfFC));
            wsheet.addCell(new Label(12, 2, "平台情况",wcfFC));
            wsheet.addCell(new Label(13, 2, "进口/国产",wcfFC));
            wsheet.addCell(new Label(14, 2, "参考单价（元）",wcfFC));
            wsheet.addCell(new Label(15, 2, "是否医保",wcfFC));
            wsheet.addCell(new Label(16, 2, "上次设备会审议结果",wcfFC));
          /*  wsheet.addCell(new Label(16, 2, "品牌同意数／总票数",wcfFC));
            wsheet.addCell(new Label(17, 2, "品牌表决结果",wcfFC));*/
            wsheet.addCell(new Label(17, 2, "型号同意数／总票数",wcfFC));
            wsheet.addCell(new Label(18, 2, "型号表决结果",wcfFC));
            // 表头结束

            wfont = new WritableFont(WritableFont.createFont("宋体"), 8,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);// 添加边框
            wcfFC.setVerticalAlignment(jxl.write.VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(jxl.write.Alignment.CENTRE);
            wcfFC.setWrap(true);


            WritableCellFormat wcfFCt = new WritableCellFormat(wfont);
            //设置背景颜色;
            wcfFCt.setBackground(Colour.GREEN);
            wcfFCt.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFCt.setVerticalAlignment(jxl.write.VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFCt.setAlignment(jxl.write.Alignment.CENTRE);

            WritableCellFormat wcfFCb = new WritableCellFormat(wfont);
            //设置背景颜色;
            wcfFCb.setBackground(Colour.RED);
            wcfFCb.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFCb.setVerticalAlignment(jxl.write.VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFCb.setAlignment(jxl.write.Alignment.CENTRE);

            WritableCellFormat wcfFCy = new WritableCellFormat(wfont);
            wcfFCy.setBackground(Colour.YELLOW);
            wcfFCy.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFCy.setVerticalAlignment(jxl.write.VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFCy.setAlignment(jxl.write.Alignment.CENTRE);

            int mmpinzhongKaishihang = 3;
            int mmpinpaiKaishihang = 3;
            int mmxinghaoKaishihang = 3;

            for (Shijipinzhong pinzhong : pinzhongs) {
                int pinpaiSize = 0;
                int isPinzhongPass = 0;
                for (Shijipinpai pinpai : pinzhong.getPinpais()) {
                    int xinghaoSize = pinpai.getXinghaos().size();
                    int i = 1;
                    for (Shijixinghao xinghao : pinpai.getXinghaos()) {
                        String mmleibie = xinghao.getLeibie() == null ? "" : xinghao.getLeibie();
                        String mmxuhao = xinghao.getOrdernumber() == null ? "" : String.valueOf(xinghao.getOrdernumber());
                        String mmxinghao = xinghao.getXinghao() == null ? "" : xinghao.getXinghao();
                        String mmpingtaibianma = xinghao.getPingtaibianma() == null ? "" : xinghao.getPingtaibianma();
                        String mmchanpinmingcheng = xinghao.getChanpinmingcheng() == null ? "" : xinghao.getChanpinmingcheng();
                        String mmchanpinguige = xinghao.getChanpinguige() == null ? "" : xinghao.getChanpinguige();
                        String mmchanpinxinghao = xinghao.getChanpinxinghao() == null ? "" : xinghao.getChanpinxinghao();
                        String mmzhucezhenghao = xinghao.getZhucezhenghao() == null ? "" : xinghao.getZhucezhenghao();
                        String mmshengchanqiye = xinghao.getShengchanqiye() == null ? "" : xinghao.getShengchanqiye();
                        String mmdanwei = xinghao.getDanwei() == null ? "" : xinghao.getDanwei();
                        String mmpingtaiqingkuang = xinghao.getPingtaiqingkuang() == null ? "" : xinghao.getPingtaiqingkuang();
                        String mmjinkouguochan = xinghao.getJinkouguochan() == null ? "" : xinghao.getJinkouguochan();
                        String mmcankaodanjia = xinghao.getCankaodanjia() == null ? "" : xinghao.getCankaodanjia();
                        String mmyibao = xinghao.getYibao() == null ? "" : xinghao.getYibao();
                        String mmbeizhu = xinghao.getBeizhu() == null ? "" : xinghao.getBeizhu();
                        String mmresult = xinghao.getResult() == null ? "" : xinghao.getResult();
                        String mmyijian;
                        wsheet.setRowView(mmxinghaoKaishihang, 580); //第三行高度
                        wsheet.addCell(new Label(0, mmxinghaoKaishihang, mmleibie, wcfFC));
                       /* wsheet.addCell(new Label(1, mmxinghaoKaishihang, mmxuhao, wcfFC));*/
                        wsheet.addCell(new Label(5, mmxinghaoKaishihang, mmpingtaibianma, wcfFC));
                        wsheet.addCell(new Label(6, mmxinghaoKaishihang, mmchanpinmingcheng, wcfFC));
                        wsheet.addCell(new Label(7, mmxinghaoKaishihang, mmchanpinguige, wcfFC));
                        wsheet.addCell(new Label(8, mmxinghaoKaishihang, mmchanpinxinghao, wcfFC));
                        wsheet.addCell(new Label(9, mmxinghaoKaishihang, mmzhucezhenghao, wcfFC));
                        wsheet.addCell(new Label(10, mmxinghaoKaishihang, mmshengchanqiye, wcfFC));
                        wsheet.addCell(new Label(11, mmxinghaoKaishihang, mmdanwei, wcfFC));
                        wsheet.addCell(new Label(12, mmxinghaoKaishihang, mmpingtaiqingkuang, wcfFC));
                        wsheet.addCell(new Label(13, mmxinghaoKaishihang, mmjinkouguochan, wcfFC));
                        wsheet.addCell(new Label(14, mmxinghaoKaishihang, mmcankaodanjia, wcfFC));
                        wsheet.addCell(new Label(15, mmxinghaoKaishihang, mmyibao, wcfFC));
                        wsheet.addCell(new Label(16, mmxinghaoKaishihang, mmbeizhu, wcfFC));
                        if("通过".equals(mmbeizhu)){
                            wsheet.addCell(new Label(4, mmxinghaoKaishihang, mmxinghao, wcfFC));
                            wsheet.addCell(new Label(17, mmxinghaoKaishihang, "", wcfFC));
                            wsheet.addCell(new Label(18, mmxinghaoKaishihang, "", wcfFC));
                        }else if("通 过".equals(mmbeizhu)){
                            if (Integer.parseInt(mmresult) > mmPassNumber) {
                                wsheet.addCell(new Label(4, mmxinghaoKaishihang, mmxinghao, wcfFCy));
                                wsheet.addCell(new Label(17, mmxinghaoKaishihang, mmresult + "/" + personCount, wcfFCy));
                                wsheet.addCell(new Label(18, mmxinghaoKaishihang, "", wcfFCy));
                            } else {
                                wsheet.addCell(new Label(4, mmxinghaoKaishihang, mmxinghao, wcfFC));
                                wsheet.addCell(new Label(17, mmxinghaoKaishihang, mmresult + "/" + personCount, wcfFC));
                                wsheet.addCell(new Label(18, mmxinghaoKaishihang, "", wcfFC));
                            }
                        }
                        else{
                            if (Integer.parseInt(mmresult) > mmPassNumber) {
                                mmyijian = "通过";
                                wsheet.addCell(new Label(4, mmxinghaoKaishihang, mmxinghao, wcfFCt));
                                wsheet.addCell(new Label(17, mmxinghaoKaishihang, mmresult + "/" + personCount, wcfFCt));
                                wsheet.addCell(new Label(18, mmxinghaoKaishihang, mmyijian, wcfFCt));
                            } else {
                                mmyijian = "不通过";
                                wsheet.addCell(new Label(4, mmxinghaoKaishihang, mmxinghao, wcfFCb));
                                wsheet.addCell(new Label(17, mmxinghaoKaishihang, mmresult + "/" + personCount, wcfFCb));
                                wsheet.addCell(new Label(18, mmxinghaoKaishihang, mmyijian, wcfFCb));
                            }
                        }
                        mmxinghaoKaishihang += 1;
                    }
                    int pinpairesult = ddMapper.selectpinpaipiaoshuByXiangmuidAndPinzhongxuhaoAndPinpai(ppxiangmuid,
                            pinzhong.getPinzhongxuhao(), pinpai.getPinpai());

                   /* String mmpinpaiyijian;
                    if (pinpairesult > mmPassNumber) {
                        mmpinpaiyijian = "通过";
                        isPinzhongPass = 1;
                        if (i < 4) {
                            wsheet.addCell(new Label(3, mmpinpaiKaishihang, pinpai.getPinpai(), wcfFCt));
                            wsheet.addCell(new Label(16, mmpinpaiKaishihang, pinpairesult + "/" + personCount, wcfFCt));
                            wsheet.addCell(new Label(17, mmpinpaiKaishihang, mmpinpaiyijian, wcfFCt));
                        } else {
                            wsheet.addCell(new Label(3, mmpinpaiKaishihang, pinpai.getPinpai(), wcfFCb));
                            wsheet.addCell(new Label(16, mmpinpaiKaishihang, pinpairesult + "/" + personCount, wcfFCb));
                            wsheet.addCell(new Label(17, mmpinpaiKaishihang, mmpinpaiyijian, wcfFCb));
                        }
                        i++;
                    } else {
                        mmpinpaiyijian = "不通过";
                        wsheet.addCell(new Label(3, mmpinpaiKaishihang, pinpai.getPinpai(), wcfFCb));
                        wsheet.addCell(new Label(16, mmpinpaiKaishihang, pinpairesult + "/" + personCount, wcfFCb));
                        wsheet.addCell(new Label(17, mmpinpaiKaishihang, mmpinpaiyijian, wcfFCb));
                    }*/
                    wsheet.addCell(new Label(3, mmpinpaiKaishihang, pinpai.getPinpai(), wcfFC));
                    wsheet.mergeCells(3, mmpinpaiKaishihang, 3, mmpinpaiKaishihang + xinghaoSize - 1);
                   /* wsheet.mergeCells(16, mmpinpaiKaishihang, 16, mmpinpaiKaishihang + xinghaoSize - 1);
                    wsheet.mergeCells(17, mmpinpaiKaishihang, 17, mmpinpaiKaishihang + xinghaoSize - 1);*/
                    mmpinpaiKaishihang += xinghaoSize;
                    pinpaiSize += xinghaoSize;
                }
               /* if (isPinzhongPass == 1) {
                    wsheet.addCell(new Label(2, mmpinzhongKaishihang, pinzhong.getPinzhongming(), wcfFCt));
                    wsheet.addCell(new Label(1, mmpinzhongKaishihang, String.valueOf(pinzhong.getPinzhongxuhao()), wcfFCt));
                } else {
                    wsheet.addCell(new Label(2, mmpinzhongKaishihang, pinzhong.getPinzhongming(), wcfFCb));
                    wsheet.addCell(new Label(1, mmpinzhongKaishihang, String.valueOf(pinzhong.getPinzhongxuhao()), wcfFCb));
                }*/
                wsheet.addCell(new Label(2, mmpinzhongKaishihang, pinzhong.getPinzhongming(), wcfFC));
                wsheet.addCell(new Label(1, mmpinzhongKaishihang, String.valueOf(pinzhong.getPinzhongxuhao()), wcfFC));
                wsheet.mergeCells(2, mmpinzhongKaishihang, 2, mmpinzhongKaishihang + pinpaiSize - 1);
                wsheet.mergeCells(1, mmpinzhongKaishihang, 1, mmpinzhongKaishihang + pinpaiSize - 1);
                mmpinzhongKaishihang += pinpaiSize;
            }
            wbook.write(); // 写入文件
            wbook.close();


        }catch(Exception ex)
        {
            ex.printStackTrace();
        }finally{
            if(os!=null){
                try {
                    os.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

    }
}
